package com.wx.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.wx.dao.StaDao;
import com.wx.model.Sta;
import com.wx.util.DBUtil;

public class StaDaoImpl implements StaDao {

	@Override
	public List<Sta> findSta(Sta sta) throws Exception {
		List<Sta> list = new ArrayList<>();
		Connection conn = DBUtil.getConnection();
		String sql="select * from sta where 1=1";
		if(sta.getStaid()>0 ){
			sql+=" and staid="+sta.getStaid();
		}
		if(sta.getStaname()!=null && sta.getStaname().length()>0){
			sql+=" and staname='%"+sta.getStaname()+"%'";
		}
		if(sta.getBrand()!=null && sta.getBrand().length()>0){
			sql+=" and brand='"+sta.getBrand()+"'"; 
		}
		if(sta.getStatype()!=null && sta.getStatype().length()>0){
			sql+=" and statype='%"+sta.getStatype()+"%'";
		}
		System.out.println(sql);
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			Sta sta2 = new Sta();
			sta2.setStaid(rs.getInt("staid"));
			sta2.setStatypeid(rs.getInt("statypeid"));
			sta2.setStaname(rs.getString("staname"));
			sta2.setBrand(rs.getString("brand"));
			sta2.setStatype(rs.getString("statype"));
			sta2.setStacount(rs.getInt("stacount"));
			sta2.setPrice(rs.getDouble("price"));
			sta2.setStatype(rs.getString("statype"));
			sta2.setDis(rs.getString("dis"));
			sta2.setSalenum(rs.getInt("salenum"));
			list.add(sta2);
		}
		System.out.println(list.size());
		rs.close();
		ps.close();
		conn.close();
		
		return list;
	}

	@Override
	public void addSta(Sta sta) throws Exception {
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = conn.prepareStatement("insert into sta(statypeid,staname,brand,statype,stacount,price,dis) values(?,?,?,?,?,?,?)");
		ps.setInt(1, sta.getStatypeid());
		ps.setString(2, sta.getStaname());
		ps.setString(3, sta.getBrand());
		ps.setString(4, sta.getStatype());
		ps.setInt(5, sta.getStacount());
		ps.setDouble(6, sta.getPrice());
		ps.setString(7, sta.getDis());
		ps.executeUpdate();
		
		ps.close();
		conn.close();
	}

	@Override
	public void modifySta(Sta sta) throws Exception {
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = conn.prepareStatement("update sta set statypeid=?,staname=?,brand=?,statype=?,stacount=?,price=?,dis=?,salenum=? where staid=?");
		ps.setInt(1, sta.getStatypeid());
		ps.setString(2, sta.getStaname());
		ps.setString(3, sta.getBrand());
		ps.setString(4, sta.getStatype());
		ps.setInt(5, sta.getStacount());
		ps.setDouble(6, sta.getPrice());
		ps.setString(7, sta.getDis());
		ps.setInt(8, sta.getSalenum());
		ps.setInt(9, sta.getStaid());
		ps.executeUpdate();
		
		ps.close();
		conn.close();
	}

	@Override
	public void deleteSta(int id) throws Exception {
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = conn.prepareStatement("delete from sta where staid=?");
		ps.setInt(1, id);
		ps.executeUpdate();
		
		ps.close();
		conn.close();
	}

}
